package test1.www.lagou.dao;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import test1.www.lagou.entity.Account;
import test1.www.lagou.entity.Phone;
import test1.www.lagou.utils.DruidUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;


/**
 * @Author : liuchangling
 * @Descrition :
 * @Date： Created in 4:18 下午 2021/5/10
 */
public class Dao {
    Connection con = null;
    PreparedStatement ps = null;

    public Dao() {
    }


    /**
     * 查询账户信息
     * @param cardOrigin
     * @return
     * @throws SQLException
     */
    public Account selectBalance(String cardOrigin) throws SQLException {
        //1.创建QueryRunner
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

        //2.编写SQL
        String sql = "select * from account where card = ?";

        //3.执行查询
        Account account = qr.query(sql, new BeanHandler<>(Account.class), cardOrigin);

        //4.获取数据
        System.out.println(account.toString());
        return account;
    }

    /**
     * 处理转账
     * @param cardOrigin
     * @param moneyNum
     * @param card
     */
    public void transfer(String cardOrigin, double moneyNum, String card) {
        try {

            // 获取连接
            con = DruidUtils.getConnection();
            // 开启事务
            con.setAutoCommit(false);
            // 发起人减钱
            String sql = "update account set balance = balance - ? where card = ?";
            ps = con.prepareStatement(sql);
            ps.setDouble(1, moneyNum);
            ps.setString(2, cardOrigin);
            ps.executeUpdate();
            //接收人加钱
            String sql2 = "update account set balance = balance + ?  where card = ?";
            ps = con.prepareStatement(sql2);
            ps.setDouble(1, moneyNum);
            ps.setString(2, card);
            ps.executeUpdate();

            //此语句只是模拟转账失败的情况，在录制视频中忘记添加这种情况
            //int i=2/0;


            // 正常情况下提交事务
            con.commit();
            System.out.println("转账成功!");
            //分别打印两个用户的余额等信息
            System.out.println(selectBalance(cardOrigin));
            System.out.println(selectBalance(card));
            //添加转账记录
            addNote(cardOrigin,moneyNum,card);
        } catch (Exception e) {
            e.printStackTrace();
            try {
                //5. 出现异常回滚事务
                con.rollback();
                System.out.println("转账失败！");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DruidUtils.close(con,ps);
        }
    }

    /**
     * 添加事务日志
     * @param cardOrigin
     * @param moneyNum
     * @param card
     * @throws SQLException
     */
    public void addNote(String cardOrigin, double moneyNum, String card) throws SQLException {
       //创建QueryRunner
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());

        String sql="insert into TRANSACTION values( ?, ?, ?, ?, ?)";

        Date date=new Date();

        // 传入转账人的信息
        Object[] param = {null,cardOrigin,"out",moneyNum,date};

        //执行 update方法
        Connection con = DruidUtils.getConnection();
        qr.update(con, sql, param);



        Object[] param2 = {null,card,"in",moneyNum,date};

        //4.执行 update方法
        qr.update(con, sql, param2);
        System.out.println("转入、转出记录成功");

        //5.释放资源
        DbUtils.closeQuietly(con);
    }

    /**
     * 查询价格高于2000元，生产日期是2019年之前的所有手机
     * @throws SQLException
     */
    public void findPhone() throws SQLException {
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from phone where price > ? and prodate < ?";

        Object[] param={2000,"2019-01-01"};

        List<Phone> list=qr.query(sql,new BeanListHandler<Phone>(Phone.class),param);

        System.out.println("价格高于2000元，生产日期是2019年之前的手机有：");
        for (Phone phone : list) {
            System.out.println(phone);
        }

    }


    /**
     * 查询所有颜色是白色的手机信息
     * @throws SQLException
     */
    public void findPhoneWhite() throws SQLException{
        QueryRunner qr=new QueryRunner(DruidUtils.getDataSource());

        String sql="select * from phone where color = ?";

        List<Phone> list=qr.query(sql,new BeanListHandler<>(Phone.class),"白色");

        System.out.println("颜色是白色的手机有：");
        for (Phone phone : list) {
            System.out.println(phone);
        }
    }



}
